package com.kungfuhacking.wristbandpro.database;

import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.kungfuhacking.wristbandpro.mqtt.MqttConstant;
import com.kungfuhacking.wristbandpro.mqtt.bean.MessageBean;
import com.kungfuhacking.wristbandpro.utils.Constant;

import java.util.ArrayList;
import java.util.List;

/**
 * 数据库操作类
 * Created by Aaron on 15/12/15.
 */
public class DatabaseManager {

    private final String TAG = getClass().getSimpleName();

    private SharedPreferences sp;

    private DatabaseManager() {
    }

    private static DatabaseManager dbManager = new DatabaseManager();

    public static DatabaseManager getInstance() {
        return dbManager;
    }

    private SQLiteDatabase db;
    private DatabaseHelper dbHelper;

    /**
     * 初始化打开数据库
     *
     * @param ctx
     * @return
     */
    private void initDb(Context ctx) {

        if (dbHelper == null)
            dbHelper = new DatabaseHelper(ctx);

        sp = ctx.getSharedPreferences(Constant.CONFIG, Context.MODE_PRIVATE);

        db = dbHelper.getWritableDatabase();

    }

//    public SQLiteDatabase getDb() {
//        return db;
//    }

    /**
     * 往历史记录表里面插入数据
     *
     * @param args 插入的数据集合
     */
    public void insertHisData(Context ctx, Object[] args) {

        synchronized (TAG) {
            initDb(ctx);

//        Log.d(TAG, " *** 保存消息到历史记录数据库 *** ");

            if (isOpen())
                db.execSQL("insert into " + MqttConstant.HISTORY_TABLE + " (sendId, sendName, sendPic, receiverTime, isRead, content, topic, " +
                        "messageType, receiveId, receiveName, receivePic, voiceLength, chatType, sendTime, userId, isListener, isReach) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", args);

            closeDataBase();
        }


    }


    /**
     * 修改发送状态
     *
     * @param ctx
     * @param sendTime 发送的时间戳, 根据时间戳查找需要修改的数据
     * @param status
     */
    public void updateReachStatus(Context ctx, long sendTime, int status, String userId, String topic) {

        synchronized (TAG) {

            initDb(ctx);
//        Cursor cursor = db.rawQuery("select * from " + MqttConstant.HISTORY_TABLE + " where sendTime=? and sendId=?",
//                new String[]{sendTime + "", userId});
//       if (cursor.getCount() > 0) {
//       }
            db.execSQL("update " + MqttConstant.HISTORY_TABLE + " set isReach=? where topic=? and userId=? and sendTime=?", new Object[]{
                    status, topic, userId, sendTime
            });
            closeDataBase();
        }
    }

    /**
     * 根据topic,sendDate,sendId 查询数据库里是否有该条消息
     *
     * @param topic
     * @param sendDate
     * @return true 有接受过该消息  false 没有接受过该消息
     */
    public boolean hasReceived(Context ctx, String topic, long sendDate, String sendId, String userId) {

        synchronized (TAG) {

            initDb(ctx);

            Cursor cursor = db.rawQuery("select * from " + MqttConstant.HISTORY_TABLE + " where topic=? and sendTime=? and sendId=? and userId=? order by _id desc limit 200",
                    new String[]{topic, sendDate + "", sendId, userId});

            Log.d(TAG, " 查询出来的消息个数 : " + cursor.getCount() + ",线程:" + Thread.currentThread().getName());

            boolean hasReceived = (cursor.getCount() > 0);
            cursor.close();
            closeDataBase();
            return hasReceived;
        }
    }

    /**
     * 查询历史聊天记录
     * 从from条 到 end条的数据 (根据topic查询)
     *
     * @param topic  sendId, sendName, sendPic," +
     *               "receiverTime, isRead, content, topic, messageType" +
     *               ", receiveId, receiveName, receivePic, voiceLength, chatType, sendTime
     * @param from   从多少开始
     * @param userId 当前登录的用户的id
     */
    public List<MessageBean> queryChatHistory(Context ctx, String userId, String topic, int from) {

//        Log.d(TAG, "   ==== 查询历史聊天记录开始 ====   " + Thread.currentThread().getName());

        synchronized (TAG) {

            initDb(ctx);

            List<MessageBean> mbList = new ArrayList<>();

            if (isOpen()) {

                //查询该topic的倒叙 从 from 到 end 的聊天记录
                try {

                    Cursor cursor = db.rawQuery("select * from " + MqttConstant.HISTORY_TABLE + " where topic=? and userId=? order by sendTime desc limit ?,?",
                            new String[]{topic, userId, from + "", MqttConstant.CHAT_HISTORY_COUNT + ""});//得到游标

                    while (cursor.moveToNext()) {

                        MessageBean mb = new MessageBean();
                        mb.id = cursor.getString(0);
                        mb.sendId = cursor.getString(1);
                        mb.sendName = cursor.getString(2);
                        mb.sendPic = cursor.getString(3);
                        mb.content = cursor.getString(6);
                        mb.messageType = cursor.getString(8);
                        mb.receiveId = cursor.getString(9);
                        mb.receiveName = cursor.getString(10);
                        mb.receivePic = cursor.getString(11);
                        mb.voiceLength = cursor.getDouble(12);
                        mb.chatType = cursor.getInt(13);
                        mb.sendDate = cursor.getLong(14);
                        mb.isListener = cursor.getInt(16);
                        mb.isReach = cursor.getInt(17);
                        mb.channelId = topic;

//                        Log.d(TAG, "发送时间sendDate: " + mb.sendDate + ",内容：" + mb.content);
                        //如果数据库里是正在发送状态,修改为发送失败状态
                        if (mb.isReach == MqttConstant.MESSAGE_SENDING) {
                            mb.isReach = MqttConstant.MESSAGE_SEND_FAIL;
                            updateReachStatus(ctx, mb.sendDate, MqttConstant.MESSAGE_SEND_FAIL, sp.getString(MqttConstant.USERID, ""), mb.channelId);
                        }

                        mbList.add(mb);

                    }

                    cursor.close();

                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

            closeDataBase();

            return mbList;
        }
    }

    /**
     * 获取topic表里面的所有聊天记录,即最近的聊天列表
     */
    public List<MessageBean> getChatListFromTopics(Context ctx, String userId) throws Exception {

//        Log.d(TAG, " 查询最近聊天记录开始 " + Thread.currentThread().getName());
        synchronized (TAG) {
            initDb(ctx);
            List<MessageBean> mbs = new ArrayList<>();

            if (db != null && db.isOpen()) {
                createTable(db);
                Cursor cursor = db.rawQuery("select * from " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " where userId=? order by sendTime desc ", new String[]{userId});//得到游标

                while (cursor.moveToNext()) {
                    if (cursor.getString(2) != null && cursor.getString(2).startsWith(MqttConstant.START_SYSTEM)) //如果是系统topic则不存储,继续查询
                        continue;
                    add2topic(mbs, cursor);
                }

                cursor.close();
            }

            closeDataBase();
//        Log.d(TAG, " 查询最近聊天记录结束 ");
            return mbs;
        }
    }

    private void add2topic(List<MessageBean> mbs, Cursor cursor) {

        MessageBean msb = new MessageBean();
        msb.receiveName = cursor.getString(1);
        msb.channelId = cursor.getString(2);
        msb.sendId = cursor.getString(3);
        msb.sendName = cursor.getString(4);
        msb.sendPic = cursor.getString(5);
        msb.receiverTime = cursor.getString(6);
        msb.receiveId = cursor.getString(7);
        msb.content = cursor.getString(8);
        msb.messageType = cursor.getString(9);
        msb.voiceLength = cursor.getDouble(10);
        msb.chatType = cursor.getInt(11);
        msb.sendDate = cursor.getLong(12);
        msb.receivePic = cursor.getString(13);
        msb.receiveName = cursor.getString(14);
        msb.isRead = cursor.getInt(15);
        msb.isListener = cursor.getInt(17);

//        Log.d(TAG, "查询的消息信息:" + msb.toString());

//        Log.d(TAG, "  未读个数 : " + msb.isRead);

        mbs.add(msb);
    }

    /**
     * 清空历史记录
     */
    public void deleteHistory(Context ctx, String topic, String userId) {

        synchronized (TAG) {

            initDb(ctx);

            if (isOpen()) {
                db.execSQL("delete from " + MqttConstant.HISTORY_TABLE + " where topic=? and userId=?", new Object[]{topic, userId});
            }

            closeDataBase();
        }
    }

    /**
     * 把最近聊天的表中的 某个topic的聊天内容置为 空
     *
     * @param topic 聊天的topic
     */
    public void clearTopic(Context ctx, String topic, String userId) {

        synchronized (TAG) {

            initDb(ctx);

            if (isOpen()) {
                createTable(db);
//            db.execSQL("delete from " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " whesre topic=? and userId=?", new Object[]{topic, userId});
                db.execSQL("update " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " set content=? where topic=? and userId=?", new Object[]{"", topic, userId});
            }

            closeDataBase();
        }
    }

    /**
     * 删除最近聊天的表中的 某个topic的聊天内容
     *
     * @param topic 聊天的topic
     */
    public void deleteTopic(Context ctx, String topic, String userId) {

        synchronized (TAG) {

            initDb(ctx);

            if (isOpen()) {
                createTable(db);
                db.execSQL("delete from " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " where topic=? and userId=?", new Object[]{topic, userId});
//                db.execSQL("update " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " set content=? where topic=? and userId=?", new Object[]{"", topic, userId});
            }

            closeDataBase();
        }
    }

    /**
     * 删除最近聊天的表中的 某个topic的聊天内容
     *
     * @param topic 聊天的topic
     */
    public void deleteTopicNosy(Context ctx, String topic, String userId) {

        if (isOpen()) {
            createTable(db);
            db.execSQL("delete from " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " where topic=? and userId=?", new Object[]{topic, userId});
//                db.execSQL("update " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " set content=? where topic=? and userId=?", new Object[]{"", topic, userId});
        }

    }


    /**
     * 把语音设置为已读(历史记录表)
     */
    public void updateListenerStatus(Context ctx, String[] args) {

        synchronized (TAG) {

            initDb(ctx);

            if (isOpen()) {
                db.execSQL("update " + MqttConstant.HISTORY_TABLE + " set isListener=? where topic=? and userId=? and sendId=? and sendTime=?", args);
            }

            closeDataBase();

        }
    }

    /**
     * 把语音设置为已读(topic表)
     */
    public void updateTopicListenerStatus(Context ctx, String[] args) {

        synchronized (TAG) {
            initDb(ctx);

            if (isOpen()) {
                createTable(db);
                db.execSQL("update " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " set isListener=? where topic=? and userId=? and sendId=? and sendTime=?", args);
            }

            closeDataBase();
        }
    }


//    /**
//     * 清空单人聊天消息
//     *
//     * @param
//     */
//    public void clearTopic(Context ctx, String[] args) {
//
//        synchronized (TAG) {
//
//            initDb(ctx);
//
//            if (isOpen()) {
//                createTable(db);
//                db.execSQL("update " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " set sendId=?,content=?" +
//                        ",messageType=?,chatType=?,receivePic=?, receiveName=?,receiveId=? where topic=? and userId=?", args);
//            }
//
//            closeDataBase();
//        }
//    }

    /**
     * 更新数据库 红点个数
     */
    public void clearRedCount(Context ctx, String[] args) {

        synchronized (TAG) {

            initDb(ctx);

            if (isOpen()) {
                createTable(db);
                db.execSQL("update " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " set isRead=? where topic=? and userId=?", args);

            }
            closeDataBase();
        }
    }

    /**
     * 更新topic表数据
     *
     * @param args 需要修改的参数
     */
    public int updateTopic(Context ctx, String[] args) {

//        Log.d(TAG, " &&&& 保存消息到topic数据库 &&&& " + Thread.currentThread().getName());
        synchronized (TAG) {

            initDb(ctx);

            int count = 0;
            long sendTime = 0; //上条消息的sendTime

            Cursor cursor = queryUnreadCount(new String[]{args[15], args[16]});

            if (cursor.getCount() > 0) {

                while (cursor.moveToNext()) {
                    sendTime = cursor.getLong(12);
                    count = cursor.getInt(15);
                }
                cursor.close();

            }

            if (sendTime > Long.parseLong(args[10])) {
                //如果上条消息的sendTime 大于 当前接受的 则当前的消息不存储,只把未读消息+1 (防止离线消息错乱)

                createTable(db);
                String[] strings = {++count + "", args[15], args[16]};
                db.execSQL("update " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " set isRead=? where topic=? and userId=?", strings);
                closeDataBase();
                return count;
            }

            if (count < 1) {
                //topic表中没有该调记录
//            LogUtil.e(TAG, " 数据库中无该条记录, 插入新topic ");
                createTable(db);
                db.execSQL("replace into " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + "(topicName, sendId, sendName, sendPic, receiverTime, receiveId" +
                        ",content, messageType, voiceLength, chatType, sendTime, receivePic, receiveName, isListener, isRead, topic, userId) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", args);

                count = 1;

            } else {

                //topic表中有记录, 更新数据
//            LogUtil.e(TAG, " 数据库中有记录,查出未读条数   ");
                args[14] = ++count + "";
                createTable(db);
                db.execSQL("update " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " set topicName=?, sendId=?, sendName=?, sendPic=?, receiverTime=?" +
                        ", receiveId=?, content=?, messageType=?, voiceLength=?, chatType=?, sendTime=?, receivePic=?" +
                        ", receiveName=?, isListener=?, isRead=? where topic=? and userId=?", args);

            }

            closeDataBase();

            return count;

        }
    }

    private Cursor queryUnreadCount(String[] args) {

        synchronized (TAG) {
            createTable(db);
            return db.rawQuery("select * from " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " where topic=? and userId=?", args);//得到游标
        }

    }

    /**
     * 删除一条数据
     */
    public void deleteData(Context ctx, MessageBean mb) {

        synchronized (TAG) {
            initDb(ctx);

            db.execSQL("delete from " + MqttConstant.HISTORY_TABLE + " where topic=? and sendId=? and sendTime=?", new Object[]{
                    mb.channelId, mb.sendId, mb.sendDate
            });
            closeDataBase();
        }
    }

    /**
     * 更新topic表数据
     * topicName 为群的名称
     *
     * @param args 需要修改的参数
     */
    public int updateTopicWithoutInfo(Context ctx, String[] args) {

        synchronized (TAG) {

            initDb(ctx);

            int count = 0;
            Cursor cursor = queryUnreadCount(new String[]{args[10], args[11]});

            if (cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    count = cursor.getInt(15);
                }
                cursor.close();
            }

            if (count < 1)
                //topic表中没有该调记录
                count = 1;
            else
                args[9] = ++count + "";

            createTable(db);
            db.execSQL("update " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " set sendId=?, sendName=?, sendPic=?, receiverTime=?, content=?" +
                    ", messageType=?, voiceLength=?, chatType=?, sendTime=?, isRead=? where topic=? and userId=?", args);

            closeDataBase();

            return count;
        }
    }


//    /**
//     * 删除topic表中的私聊和群聊
//     */
//    public void clearChat(Context ctx) {
//
//        synchronized (TAG) {
//
//            initDb(ctx);
//
//            Cursor cursor = db.rawQuery("select * from " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, ""), null);//得到游标
//
//            while (cursor.moveToNext()) {
//                String topic = cursor.getString(2);
//
//                if (topic.startsWith(MqttConstant.START_GROUP) ||
//                        topic.startsWith(MqttConstant.START_SINGLE)) {
//                    //如果是群聊或者私聊
//                    deleteTopicNosy(ctx, topic, sp.getString(MqttConstant.USERID, ""));
//
//                }
//
//            }
//
//            closeDataBase();
//
//        }
//
//    }


    /**
     * 检查topic表, 如果没有新的老人的topic记录 则添加一条空记录
     */
    public void insertEmptyContent(Context ctx, String topic, String topicName, String topicImg, String userId) {

        synchronized (TAG) {

            initDb(ctx);

            boolean b = queryTopic(topic, userId);

            if (!b) {
                db.execSQL("insert into " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " (topicName, topic, " +
                                "content, sendId, receivePic, messageType, receiveName, userId) values(?,?,?,?,?,?,?,?)",
                        new Object[]{topicName, topic, "", userId, topicImg, MqttConstant.MQTTMESSAGETEXT, topicName, userId});
            }

            closeDataBase();

        }
    }

    //查询topic表, 查看
    private boolean queryTopic(String topic, String userId) {

        Cursor cursor = db.rawQuery("select * from " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " where topic=? and userId=?", new String[]{topic, userId});
        boolean b = cursor.getCount() > 0;
        cursor.close();
        return b;
    }


    /**
     * 查询所有的未读消息个数
     *
     * @return
     */
    public int queryAllUnReadCount(Context ctx, String userId) {

        synchronized (TAG) {

            initDb(ctx);

            int sum = 0;
            createTable(db);
            Cursor cursor = db.rawQuery("select sum(isRead) from " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") + " where userId=?", new String[]{userId});

            while (cursor.moveToNext()) {
                sum = cursor.getInt(0);
            }

            cursor.close();
            closeDataBase();

            return sum;
        }
    }

    /**
     * 关闭db
     */
    private void closeDataBase() {

        if (isOpen()) {
            db.close();
            db = null;
        }

    }

    /**
     * 数据库是否是打开的状态
     *
     * @return true 打开, false 关闭状态
     */
    public boolean isOpen() {
        return db != null && db.isOpen();
    }

    /**
     * 如果topic表不存在 则创建topic表
     *
     * @param db
     */
    private void createTable(SQLiteDatabase db) {

        db.execSQL("CREATE TABLE IF NOT EXISTS " + MqttConstant.TOPIC_TABLE + sp.getString(MqttConstant.USERID, "") +
                " (_id INTEGER , topicName VARCHAR, topic VARCHAR, sendId VARCHAR, " +
                "sendName VARCHAR, sendPic VARCHAR," + "receiverTime VARCHAR, receiveId VARCHAR, content VARCHAR," +
                " messageType VARCHAR, voiceLength VARCHAR, chatType VARCHAR, sendTime VARCHAR, receivePic VARCHAR, " +
                "receiveName VARCHAR, isRead INTEGER, userId VARCHAR, isListener VARCHAR, PRIMARY KEY(topic))");
    }


}
